# -*- coding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU Affero General Public License as
#    published by the Free Software Foundation, either version 3 of the
#    License, or (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU Affero General Public License for more details.
#
#    You should have received a copy of the GNU Affero General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################
import xlwt
import math
from .. import format_common
from openerp.tools.translate import _
import cStringIO
from openerp.osv import osv, fields
import os, time
import base64
from openerp.tools import float_round, float_is_zero, float_compare
import logging
from babel.numbers import format_decimal
_logger = logging.getLogger(__name__)
from xlwt import *

class delivery_report(osv.osv_memory):
     
    _name = 'delivery.report'
    _description = 'Delivery Report'
    
    _columns = {
        'date_filter_selection': fields.selection([('none','None'),('date','Date')],'Date Filter Selection'),
        'so_filter_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'SO Filter Selection', required=True),
        'do_filter_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'Do Filter Selection', required=True),
        'cust_filter_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'Customer Filter Selection', required=True), 
        'curr_filter_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'Currency Filter Selection', required=True),
        'so_ids' :fields.many2many('sale.order', 'report_delivery_order_rel', 'report_id', 'so_id', 'Sale Order', domain=[('state','!=','cancel')]),
        'do_ids' :fields.many2many('stock.picking', 'report_delivery_stock_picking_rel', 'report_id', 'do_id', 'DO', domain=[('state','=','done'),('picking_type_id','=',2)]),
        'partner_ids' :fields.many2many('res.partner', 'report_delivery_supplier_rel', 'report_id', 'partner_id', 'Customer', domain=[('customer','=',True)]),
        'currency_ids' :fields.many2many('res.currency', 'report_delivery_currency_rel', 'report_id', 'currency_id', 'Currency'),
        'so_no_def_from':fields.many2one('sale.order', 'SO NO From', domain=[('state','!=','cancel')], required=False),
        'so_no_def_to':fields.many2one('sale.order', 'SO NO To', domain=[('state','!=','cancel')], required=False),
        'so_no_input_from':fields.char('SO NO From', size=128),
        'so_no_input_to':fields.char('SO NO To', size=128),
        'cust_name_def_from':fields.many2one('res.partner', 'Customer Name From', domain=[('customer','=',True)], required=False),
        'cust_name_def_to':fields.many2one('res.partner', 'Customer Name To', domain=[('customer','=',True)], required=False),
        'cust_name_input_from':fields.char('Customer Name From', size=128),
        'cust_name_input_to':fields.char('Customer Name To', size=128),
        'curr_name_def_from':fields.many2one('res.currency', 'Currency Name From', required=False),
        'curr_name_def_to':fields.many2one('res.currency', 'Currency Name To', required=False),
        'curr_name_input_from':fields.char('Currency Name From', size=128),
        'curr_name_input_to':fields.char('Currency Name To', size=128),
        'do_no_def_from':fields.many2one('stock.picking', 'DO NO From', domain=[('state','=','done'),('picking_type_id','=',2)], required=False),
        'do_no_def_to':fields.many2one('stock.picking', 'DO NO To', domain=[('state','=','done'),('picking_type_id','=',2)], required=False),
        'do_no_input_from':fields.char('DO NO From', size=128),
        'do_no_input_to':fields.char('DO NO To', size=128),
        'date_from': fields.date("From Date"),
        'date_to': fields.date("To Date"),
        'sortby_ids': fields.one2many('delivery.sortby','sortby_id', 'Sort By'),
          
    }
    
    _defaults = { 
        'date_filter_selection': 'none',
        'so_filter_selection': 'all_vall',
        'do_filter_selection': 'all_vall',
        'cust_filter_selection': 'all_vall',
        'curr_filter_selection': 'all_vall',   
    }
    
    def onchange_filter_so_selection(self, cr, uid, ids, filter_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if filter_selection != 'all_vall':
            
             res['value'] = {'so_no_def_from': False, 'so_no_def_to': False, 'so_no_input_from': False, 'so_no_input_to': False, 'so_ids': False,
                                }
        return res
    
    def onchange_filter_do_selection(self, cr, uid, ids, filter_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if filter_selection != 'all_vall':
            
             res['value'] = {'do_no_def_from': False, 'do_no_def_to': False, 'do_no_input_from': False, 'do_no_input_to': False, 'do_ids': False,
                                }
        return res
    
    def onchange_filter_cust_selection(self, cr, uid, ids, filter_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if filter_selection != 'all_vall':
            
             res['value'] = {'cust_name_def_from': False, 'cust_name_def_to': False, 'cust_name_input_from': False, 'cust_name_input_to': False, 'partner_ids': False,
                                }
        return res
    
    def onchange_filter_curr_selection(self, cr, uid, ids, filter_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if filter_selection != 'all_vall':
            
             res['value'] = {'curr_name_def_from': False, 'curr_name_def_to': False, 'curr_name_input_from': False, 'curr_name_input_to': False, 'currency_ids': False,
                                }
        return res
    
    def onchange_date_selection(self, cr, uid, ids, date_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if date_selection != 'none':
            
             res['value'] = {'date_from': False, 'date_to': False, 
                                }
        return res
    
    def create_vat(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        datas = {'ids': context.get('active_ids', [])}
        datas['model'] = 'delivery.report'
        datas['form'] = self.read(cr, uid, ids)[0]
         
        return {
            'type': 'ir.actions.report.xml',
            'report_name': 'delivery.report.pdf',
            'datas': datas,
        }  
     
    def _search_data(self, cr, uid, ids, data_obj, val_data, filter_selection, default_from, default_to, input_from, input_to, selection_ids, context=None):
        #Out Default Value
        data_header = ''
        data_ids = []
        #Declaration
 
        if context is None:
            context = {}
          
#         special_case = 'special_case' in context and context['special_case'] or False  
        if filter_selection == 'all_vall':
            data_ids = data_obj.search(cr, uid, val_data, order='id ASC')
        if filter_selection == 'def':
            data_found = False 
            if default_from and data_obj.browse(cr, uid, default_from) and data_obj.browse(cr, uid, default_from).name:
                default_from_header = data_obj.browse(cr, uid, default_from).name
                data_found = True
                val_data.append(('name', '>=', default_from_header)) 
            if default_to and data_obj.browse(cr, uid, default_to) and data_obj.browse(cr, uid, default_to).name:
                default_to_header = data_obj.browse(cr, uid, default_to).name
                data_found = True
                val_data.append(('name', '<=', default_to_header)) 
            if data_found:
                data_header = '"' + default_from_header + '" - "' + default_to_header + '"'
                data_ids = data_obj.search(cr, uid, val_data, order='id ASC') 
        if filter_selection == 'input':
            data_found = False 
            if input_from:
                data_ids_from = data_obj.search(cr, uid, [('name','=ilike',input_from + '%')], order="name", limit=1)
                if data_ids_from:
                    input_from_name = data_obj.browse(cr, uid, data_ids_from[0]).name 
                    data_found = True
                    val_data.append(('name', '>=', input_from_name))
            if input_to:    
                data_ids_to = data_obj.search(cr, uid, [('name','=ilike', input_to + '%')], order="name desc", limit=1)
                if data_ids_to:
                    input_to_name = data_obj.browse(cr, uid, data_ids_to[0]).name
                    data_found = True
                    val_data.append(('name', '<=', input_to_name))
            data_header = '"' + input_from + '" - "' + input_to + '"'
            if data_found:
                data_ids = data_obj.search(cr, uid, val_data, order='id ASC')
        if filter_selection == 'selection':
            if selection_ids:
                sel_ids = '' 
                for sel in  data_obj.browse(cr, uid, selection_ids):
                    sel_ids += ('"%s",')%(sel.name,) 
                data_header = '[' + sel_ids +']'
                data_ids =selection_ids
        return data_header, data_ids
 
    def _build_contexts(self, cr, uid, ids, data, context=None):
        if context is None:
            context = {}
              
#         curr_name_def = data['form']['curr_name_def'] or False
#         curr_name_input = data['form']['curr_name_input'] or False 
        sale_orderline_obj    = self.pool.get('sale.order.line') 
        sale_order_obj      = self.pool.get('sale.order')
        stock_picking_obj     = self.pool.get('stock.picking')
        stock_move_obj     = self.pool.get('stock.move') 
        res_currency_obj     = self.pool.get('res.currency') 
           
        result = {}
        result['date_filter_selection'] = data['form']['date_filter_selection']
        result['so_filter_selection'] = data['form']['so_filter_selection']
        result['do_filter_selection'] = data['form']['do_filter_selection']
        result['cust_filter_selection'] = data['form']['cust_filter_selection']
        result['curr_filter_selection'] = data['form']['curr_filter_selection']
         
        result['sortby_ids'] = data['form']['sortby_ids'] or False
  
        result['output_cust_header'], result['cust_ids'] = self._search_data(cr, uid, ids, self.pool.get('res.partner'),
                                                                                      [],
                                                                                      data['form']['cust_filter_selection'],
                                                                                      data['form']['cust_name_def_from'] or False,
                                                                                      data['form']['cust_name_def_to'] or False,
                                                                                      data['form']['cust_name_input_from'] or False,
                                                                                      data['form']['cust_name_input_to'] or False,
                                                                                      data['form']['partner_ids'] or False,
                                                                                      context=context)
         
        result['output_do_header'], result['do_no_ids'] = self._search_data(cr, uid, ids, self.pool.get('stock.picking'),
                                                                                      [],
                                                                                      data['form']['do_filter_selection'],
                                                                                      data['form']['do_no_def_from'] or False,
                                                                                      data['form']['do_no_def_to'] or False,
                                                                                      data['form']['do_no_input_from'] or False,
                                                                                      data['form']['do_no_input_to'] or False,
                                                                                      data['form']['do_ids'] or False,
                                                                                      context=context)
    
 
        date_from = data['form']['date_from']
        date_to = data['form']['date_to']
         
        output_date_header = ''
        val_date = []
        if data['form']['date_filter_selection'] == 'date':
            if date_from and not date_to:
                output_date_header = 'Date Start From : %s' % (date_from,)
                val_date.append(('date_done','>=',str(date_from)))
            elif date_to and not date_from:
                output_date_header = 'Date Until : %s' % (date_to,)
                val_date.append(('date_done','<=',str(date_to))) 
            elif date_to and date_from:
                output_date_header = 'Date Start From : %s Until: %s' % (date_from, date_to)
                val_date.append(('date_done','>=',str(date_from)))
                val_date.append(('date_done','<=',str(date_to)))
         
        so_filter_selection = data['form']['so_filter_selection'] 
        so_no_def_from = data['form']['so_no_def_from'] or False
        so_no_def_to = data['form']['so_no_def_to'] or False     
        so_no_input_from = data['form']['so_no_input_from'] or False
        so_no_input_to = data['form']['so_no_input_to'] or False
        so_ids = data['form']['so_ids'] or False
         
        val_data =[]  
        stock_picking_ids = []
        data_header = ''   
        if so_filter_selection == 'def':
            data_found = False 
            if so_no_def_from :
                so_from_stock_move_ids = sale_order_obj.search(cr, uid, [('id', '=', so_no_def_from)])
                default_from_header = sale_order_obj.browse(cr, uid, so_from_stock_move_ids).name
                data_found = True 
                val_data.append(('procurement_id.sale_line_id.order_id.name', '>=', default_from_header)) 
            if so_no_def_to :
                so_to_stock_move_ids = sale_order_obj.search(cr, uid, [('id', '=', so_no_def_to)])
                default_to_header = sale_order_obj.browse(cr, uid, so_to_stock_move_ids).name
                data_found = True 
                val_data.append(('procurement_id.sale_line_id.order_id.name', '<=', default_to_header)) 
            if data_found:
                stock_p_ids = []
                data_header = '"' + default_from_header + '" - "' + default_to_header + '"'
                data_stock_move_so_ids = stock_move_obj.search(cr, uid, val_data,order='id ASC')  
                for sp in stock_move_obj.browse(cr, uid, data_stock_move_so_ids) :
                    stock_p_ids.append((sp.picking_id.id)) 
                stock_picking_ids = stock_p_ids 
        if so_filter_selection == 'input':
            data_found = False 
            if so_no_input_from:   
                data_ids_from = sale_order_obj.search(cr, uid, [('name','=ilike',so_no_input_from + '%')], order="name", limit=1)
                if data_ids_from:
                    input_from_name = sale_order_obj.browse(cr, uid, data_ids_from[0]).name 
                    data_found = True
                    val_data.append(('procurement_id.sale_line_id.order_id.name', '>=', input_from_name))
            if so_no_input_to:    
                data_ids_to = sale_order_obj.search(cr, uid, [('name','=ilike', so_no_input_to + '%')], order="name desc", limit=1)
                if data_ids_to:
                    input_to_name = sale_order_obj.browse(cr, uid, data_ids_to[0]).name
                    data_found = True
                    val_data.append(('procurement_id.sale_line_id.order_id.name', '<=', input_to_name)) 
            data_header = '"' + so_no_input_from + '" - "' + so_no_input_to + '"'
            if data_found:
                stock_p_ids = []
                data_stock_move_so_ids = stock_move_obj.search(cr, uid, val_data,order='id ASC')  
                for sp in stock_move_obj.browse(cr, uid, data_stock_move_so_ids) :
                    stock_p_ids.append((sp.picking_id.id)) 
                stock_picking_ids = stock_p_ids
        if so_filter_selection == 'selection':
            if so_ids:
                sel_ids = ''
                stock_p_ids = [] 
                for sel in  sale_order_obj.browse(cr, uid, so_ids):
                    sel_ids += ('"%s",')%(sel.name,) 
                data_header = '[' + sel_ids +']'
                data_stock_move_so_ids = stock_move_obj.search(cr, uid, [('procurement_id.sale_line_id.order_id.id', 'in', so_ids)])
                for sp in stock_move_obj.browse(cr, uid, data_stock_move_so_ids) :
                    stock_p_ids.append((sp.picking_id.id)) 
                stock_picking_ids = stock_p_ids
         
        curr_filter_selection = data['form']['curr_filter_selection'] 
        curr_name_def_from = data['form']['curr_name_def_from'] or False
        curr_name_def_to = data['form']['curr_name_def_to'] or False     
        curr_name_input_from = data['form']['curr_name_input_from'] or False
        curr_name_input_to = data['form']['curr_name_input_to'] or False
        currency_ids = data['form']['currency_ids'] or False
         
        val_data_curr =[]  
        curr_ids = []
        data_header_curr = ''   
        if curr_filter_selection == 'def':
            data_found = False 
            if curr_name_def_from :
                curr_from_stock_move_ids = res_currency_obj.search(cr, uid, [('id', '=', curr_name_def_from)])
                default_from_header = res_currency_obj.browse(cr, uid, curr_from_stock_move_ids).name
                data_found = True 
                val_data_curr.append(('procurement_id.sale_line_id.order_id.pricelist_id.currency_id.name', '>=', default_from_header)) 
            if curr_name_def_to :
                curr_to_stock_move_ids = res_currency_obj.search(cr, uid, [('id', '=', curr_name_def_to)])
                default_to_header = res_currency_obj.browse(cr, uid, curr_to_stock_move_ids).name
                data_found = True 
                val_data_curr.append(('procurement_id.sale_line_id.order_id.pricelist_id.currency_id.name', '<=', default_to_header)) 
            if data_found:
                currency_at_so_ids = []
                data_header_curr = '"' + default_from_header + '" - "' + default_to_header + '"'
                data_stock_move_currency_ids = stock_move_obj.search(cr, uid, val_data_curr,order='id ASC')  
                for curr in stock_move_obj.browse(cr, uid, data_stock_move_currency_ids) :
                    currency_at_so_ids.append((curr.picking_id.id)) 
                curr_ids = currency_at_so_ids 
        if curr_filter_selection == 'input':
            data_found = False 
            if curr_name_input_from:   
                data_ids_from = res_currency_obj.search(cr, uid, [('name','=ilike',curr_name_input_from + '%')], order="name", limit=1)
                if data_ids_from:
                    input_from_name = res_currency_obj.browse(cr, uid, data_ids_from[0]).name 
                    data_found = True
                    val_data_curr.append(('procurement_id.sale_line_id.order_id.pricelist_id.currency_id.name', '>=', input_from_name))
            if curr_name_input_to:    
                data_ids_to = res_currency_obj.search(cr, uid, [('name','=ilike', curr_name_input_to + '%')], order="name desc", limit=1)
                if data_ids_to:
                    input_to_name = res_currency_obj.browse(cr, uid, data_ids_to[0]).name
                    data_found = True
                    val_data_curr.append(('procurement_id.sale_line_id.order_id.pricelist_id.currency_id.name', '<=', input_to_name)) 
            data_header_curr = '"' + curr_name_input_from + '" - "' + curr_name_input_to + '"'
            if data_found:
                currency_at_so_ids = []
                data_stock_move_currency_ids = stock_move_obj.search(cr, uid, val_data_curr,order='id ASC')  
                for curr in stock_move_obj.browse(cr, uid, data_stock_move_currency_ids) :
                    currency_at_so_ids.append((curr.picking_id.id)) 
                curr_ids = currency_at_so_ids
        if curr_filter_selection == 'selection':
            if currency_ids:
                sel_ids = ''
                currency_stock_picking_ids = [] 
                for sel in  res_currency_obj.browse(cr, uid, currency_ids):
                    sel_ids += ('"%s",')%(sel.name,) 
                data_header_curr = '[' + sel_ids +']'
                data_stock_move_currency_ids = stock_move_obj.search(cr, uid, [('procurement_id.sale_line_id.order_id.pricelist_id.currency_id.id', 'in', currency_ids)])
                for curr in stock_move_obj.browse(cr, uid, data_stock_move_currency_ids) :
                    currency_stock_picking_ids.append((curr.picking_id.id)) 
                curr_ids = currency_stock_picking_ids
                   
        result['data_stock_picking_curr_ids']=curr_ids
        result['data_stock_picking_curr_header']=data_header_curr 
         
        result['data_stock_picking_so_ids']=stock_picking_ids
        result['data_stock_picking_so_header']=data_header 
         
        result['output_date_header'] = output_date_header
        result['val_date'] = val_date
              
        return result
             
      
    def print_report(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        data = {}
        data['ids'] = context.get('active_ids', [])
        data['model'] = context.get('active_model', 'ir.ui.menu')
        data['form'] = self.read(cr, uid, ids, ['date_filter_selection','so_filter_selection',\
                                                'curr_filter_selection','do_filter_selection','cust_filter_selection',\
                                                'date_from', 'date_to',\
                                                'so_no_def_from','so_no_def_to',\
                                                'so_no_input_from','so_no_input_to',\
                                                'cust_name_def_from','cust_name_def_to',\
                                                'cust_name_input_from','cust_name_input_to',\
                                                'curr_name_def_from','curr_name_def_to','curr_name_input_from','curr_name_input_to',\
                                                'do_no_def_from','do_no_def_to',\
                                                'do_no_input_from','do_no_input_to','so_ids',\
                                                'do_ids','partner_ids','sortby_ids','currency_ids'], context=context)[0]
        for field in ['date_filter_selection','so_filter_selection',\
                                                'curr_filter_selection','do_filter_selection','cust_filter_selection',\
                                                'date_from', 'date_to',\
                                                'so_no_def_from','so_no_def_to',\
                                                'so_no_input_from','so_no_input_to',\
                                                'cust_name_def_from','cust_name_def_to',\
                                                'cust_name_input_from','cust_name_input_to',\
                                                'curr_name_def_from','curr_name_def_to','curr_name_input_from','curr_name_input_to',\
                                                'do_no_def_from','do_no_def_to',\
                                                'do_no_input_from','do_no_input_to','so_ids',\
                                                'do_ids','partner_ids','sortby_ids','currency_ids']:
            if isinstance(data['form'][field], tuple):
                data['form'][field] = data['form'][field][0]
            
        used_context = self._build_contexts(cr, uid, ids, data, context=context)
 
        return self._get_tplines_xls(cr, uid, ids, used_context, context=context)
      
    def _get_tplines_xls(self, cr, uid, ids,data, context):
        form = data
  
        if not ids:
            ids = data['ids']
        if not ids:
            return []
          
        sale_orderline_obj    = self.pool.get('sale.order.line') 
        sale_order_obj      = self.pool.get('sale.order')
        stock_picking_obj     = self.pool.get('stock.picking')
        stock_move_obj     = self.pool.get('stock.move') 
        partner_obj     = self.pool.get('res.partner')
        company_obj = self.pool.get('res.company')
        currency_obj = self.pool.get('res.currency')
        user_obj = self.pool.get('res.users')
        dp_obj = self.pool.get('decimal.precision')
        delivery_sortby_obj = self.pool.get('delivery.sortby')
         
        results1        = []
        result        = []
        balance_by_cur = {} 
         
        val_delivery = []
          
        data_stock_picking_so_ids = form['data_stock_picking_so_ids']
        output_so_header = form['data_stock_picking_so_header']
        data_stock_picking_curr_ids = form['data_stock_picking_curr_ids']
        output_currency_header = form['data_stock_picking_curr_header'] 
        cust_ids = form['cust_ids']
        do_no_ids = form['do_no_ids'] 
        val_date = form['val_date'] 
        output_do_header = form['output_do_header']
        output_cust_header = form['output_cust_header'] 
        output_date_header = form['output_date_header']
         
         
        workbook = xlwt.Workbook()
        M_header_tstyle = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=300)
        report_tstyle = format_common.font_style(position='left', bold=1, fontos='black', font_height=190)
        report_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        header_tstyle = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        header_tstyle_c = format_common.font_style(position='center', bold=1, border=1, fontos='black', font_height=160)
        header_tstyle_l = format_common.font_style(position='left', bold=1,border=1, fontos='black', font_height=180)
        header_tstyle_c_Top = format_common.font_style(position='center', bold=1, border=4, fontos='black', font_height=180)
        header_tstyle_c_Bottom = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=180)
        header_tstyle_r = format_common.font_style(position='right', bold=1,  border=1,fontos='black', font_height=180)
        view_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        view_tstyle_c = format_common.font_style(position='center', border=1, fontos='black', font_height=150)
        view_tstyle_l = format_common.font_style(position='left',border=1, fontos='black', font_height=150)
        view_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        view_tstyle_r_b = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        other_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        other_tstyle_c = format_common.font_style(position='center', fontos='black', font_height=180)
        other_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        text_tstyle = format_common.font_style(position='left', bold=1, border=1, fontos='black', font_height=180)
        text_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=220)
        text_tstyle2 = format_common.font_style(position='left', fontos='black', font_height=220)
        text_tstyle_c = format_common.font_style(position='center', bold=1, fontos='black', font_height=180)
        text_tstyle_l = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        text_tstyle_r = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        text_tstyle_r_closing = format_common.font_style(position='right', bold=1,border=7, fontos='black', font_height=180)
        text_tstyle_Top = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Bottom = format_common.font_style(position='left', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_Top_r = format_common.font_style(position='right', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Top_l = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Top_r_r = format_common.font_style(position='right', bold=1, border=6, fontos='black', font_height=180)
        text_tstyle_Top_l_l = format_common.font_style(position='left', bold=1, border=6, fontos='black', font_height=180)
        text_tstyle_Bottom_r = format_common.font_style(position='right', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_TopBottom = format_common.font_style(position='left', bold=1, border=2, fontos='black', font_height=180)
        text_tstyle_TopBottom_r = format_common.font_style(position='right', bold=1, border=2, fontos='black', font_height=180)
        view_tstyle_r_b_170 = format_common.font_style(position='right', bold=1, fontos='black', font_height=170)
         
         ## decimat format number
        dp_data = {}
        decimal_format = easyxf(num_format_str = '0.000000')
        dp_ids = dp_obj.search(cr, uid, [])
          
        for dp_id in dp_obj.browse(cr, uid, dp_ids, context=None) :
            dp_data[dp_id.name] = dp_id.digits
         
        #get data decimal precision by name 
        dp_account = dp_data['Account']
        dp_product_price = dp_data['Product Price']
        dp_product_uom = dp_data['Product Unit of Measure']
         
        #digit by account
        digits = int(math.pow(10, dp_account))
        digit_output = '#,##0.' + str(digits)[1:]
 
        currency_format = easyxf(num_format_str = digit_output)
         
        credit_limit_format = easyxf('font: bold 1; border: top thin')
        credit_limit_format.num_format_str = digit_output
         
        currency_ttl_format = easyxf('font: bold 1')
        currency_ttl_format.num_format_str = digit_output
         
        total_ttl_format_closing = easyxf('font: bold 1; border: top thick, bottom thick, left thick, right thick')
        total_ttl_format_closing.num_format_str = digit_output
 
        currency_ttl_format_tlrb_view = easyxf('border: top thin, bottom thin, left thin, right thin; font: name Verdana; font: height 150')
        currency_ttl_format_tlrb_view.num_format_str = digit_output
         
        currency_ttl_format_tlrb = easyxf('font: bold 1; border: top thick, bottom thick, left thick, right thick')
        currency_ttl_format_tlrb.num_format_str = digit_output
         
         
        #digit by unit price
        digits_up = int(math.pow(10, dp_product_price))
        digit_output_up = '#,##0.' + str(digits_up)[1:]
 
        unit_price_format = easyxf(num_format_str = digit_output_up)
         
        unit_price_ttl_format_tlrb_view = easyxf('border: top thin, bottom thin, left thin, right thin; font: name Verdana; font: height 150')
        unit_price_ttl_format_tlrb_view.num_format_str = digit_output_up
        
        unit_price_r_b_view =  easyxf('font: bold 1; font: name Verdana; font: height 170')
        unit_price_r_b_view.num_format_str = digit_output_up 
        #digit by unit of measure
        digits_uom = int(math.pow(10, dp_product_uom))
        digit_output_uom = '#,##0.' + str(digits_uom)[1:]
 
        unit_of_measure_format = easyxf(num_format_str = digit_output_uom)
         
        unit_of_measure_ttl_format_tlrb_view = easyxf('border: top thin, bottom thin, left thin, right thin; font:name Verdana; font: height 150')
        unit_of_measure_ttl_format_tlrb_view.num_format_str = digit_output_uom
         
        #### decimat format number
         
      
        name_file = 'Delivery Report.xls'
        sheet = workbook.add_sheet('Delivery Report  XLS')
#             sheet.row(0).height = 350
          
        row=0
         
        company_name = user_obj.browse(cr, uid, uid).company_id.name
        sheet.write_merge(row, row, 0, 9, company_name, report_tstyle1)
         
        #header titile 
        row+=1
        sheet.write_merge(row, row, 0, 9, 'Delivery Report', report_tstyle1) 
             
        #printed date
        user_ids = user_obj.search(cr, uid, [('id','=',uid)])
        if user_ids :
            tz = user_obj.browse(cr, uid, user_ids[0])
            tz_name = tz.tz
            user_name = tz.name
        os.environ['TZ'] = tz_name #set time zone secara otomatis tidak hard code
        time.tzset()
        row+=1
        sheet.write_merge(row, row, 0, 4, 'Printed date : ' + time.strftime("%Y/%m/%d") + '  Time : '+ time.strftime("%H:%M:%S"), report_tstyle1)
        sheet.write_merge(row, row, 5, 9, 'Printed by : ' +user_name , report_tstyle1)
         
          
        if 'date_filter_selection' in form :
            if form['date_filter_selection'] != 'none':
                row += 1 
                sheet.write_merge(row, row, 0, 9, output_date_header, report_tstyle1)
                 
        if 'so_filter_selection' in form :
            if form['so_filter_selection'] != 'all_vall' :
                row += 1
                sheet.write(row, 0, 'SO No. : ', report_tstyle1)
                sheet.write_merge(row, row, 1, 9, output_so_header, report_tstyle1)
         
        if 'do_filter_selection' in form :
            if form['do_filter_selection'] != 'all_vall':
                row += 1
                sheet.write(row, 0, 'DO No. : ', report_tstyle1)
                sheet.write_merge(row, row, 1, 9, output_do_header, report_tstyle1)
          
        if 'cust_filter_selection' in form :   
            if form['cust_filter_selection'] != 'all_vall':
                row += 1
                sheet.write(row, 0, 'Customer : ', report_tstyle1)
                sheet.write_merge(row, row, 1, 9, output_cust_header, report_tstyle1)
         
        if 'curr_filter_selection' in form :   
            if form['curr_filter_selection'] != 'all_vall':
                row += 1
                sheet.write(row, 0, 'Currency : ', report_tstyle1)
                sheet.write_merge(row, row, 1, 9, output_currency_header, report_tstyle1)
         
         
        name_of_sort_by ='' 
        output_sort_name ='' 
        if form['sortby_ids']:
            delivery_sortby_ids = delivery_sortby_obj.search(cr, uid, [('id', 'in', form['sortby_ids'])], order='seq asc') 
            for sort_by in delivery_sortby_obj.browse(cr, uid, delivery_sortby_ids, context=None):
                if sort_by.sort_by == 'date_done' :
                    name_of_sort_by = 'Delivery Date '
                elif sort_by.sort_by == 'origin' :  
                    name_of_sort_by = 'SO No. '
                elif sort_by.sort_by == 'name' :  
                    name_of_sort_by = 'DO No. '
                elif sort_by.sort_by == 'partner_id' :  
                    name_of_sort_by = 'Customer '
                elif sort_by.sort_by == 'curr_name' :  
                    name_of_sort_by = 'Currency '    
                output_sort_name += ('"%s",')%(name_of_sort_by,)        
            row += 1
            sheet.write(row, 0, 'Sort By : ', report_tstyle1)
            sheet.write_merge(row, row, 1, 9,output_sort_name, report_tstyle1)
                        
#         row+=1         
#         sheet.write_merge(row, row, 0, 9, '', text_tstyle_c)
        #Header Name
           
        #Column Width
        sheet.col(0).width = 256 * 14
        sheet.col(1).width = 256 * 11
        sheet.col(2).width = 256 * 13
        sheet.col(3).width = 256 * 12
        sheet.col(4).width = 256 * 26
        sheet.col(5).width = 256 * 20
        sheet.col(6).width = 256 * 10
        sheet.col(7).width = 256 * 10
        sheet.col(8).width = 256 * 13
        sheet.col(9).width = 256 * 13
           
        val_delivery.append(('state','=','done'))
        val_delivery.append(('picking_type_id','=',2)) 
          
        if data_stock_picking_so_ids: 
            val_delivery.append(('id','in',data_stock_picking_so_ids))
        if cust_ids:
            val_delivery.append(('partner_id','in',cust_ids)) 
        if do_no_ids: 
            val_delivery.append(('id','in',do_no_ids))
        if data_stock_picking_curr_ids:
            val_delivery.append(('id','in',data_stock_picking_curr_ids))
        if 'val_date' in form:
            val_delivery += form['val_date'] 
           
          
        delivery_sortby_ids = []
        if 'sortby_ids' in form:
            delivery_sortby_ids = delivery_sortby_obj.search(cr, uid, [('id', 'in', form['sortby_ids'])], order='seq desc')
          
        stock_picking_ids=stock_picking_obj.search(cr, uid,val_delivery)
         
        for stock_pickingg in stock_picking_obj.browse(cr, uid, stock_picking_ids, context=None):
             
            move_lines = []
            curr_name = ''
            total = 0  
            move_lines_ids=stock_move_obj.search(cr, uid,[('picking_id','=',stock_pickingg.id)])
            for stock_move_line in stock_move_obj.browse(cr, uid, move_lines_ids, context=None) :
                move_lines.append({  
                    'description' : stock_move_line.name,
                    'product_qty' : stock_move_line.product_uom_qty,
                    'currency_name': stock_move_line.procurement_id.sale_line_id.order_id.pricelist_id.currency_id.name,
                    'price_unit': stock_move_line.procurement_id.sale_line_id.price_unit,
                    'subtotal': (stock_move_line.product_uom_qty * stock_move_line.procurement_id.sale_line_id.price_unit),
                   })
                total += (stock_move_line.product_uom_qty * stock_move_line.procurement_id.sale_line_id.price_unit)
#             move_lines = move_lines and sorted(move_lines, key=lambda val_res: val_res['currency_name']) or []
                curr_name =  stock_move_line.procurement_id.sale_line_id.order_id.pricelist_id.currency_id.name  
            results1.append({
                    'date_done' : stock_pickingg.date_done[:10],        
                    'origin' : stock_pickingg.origin,
                    'name' :stock_pickingg.name,
                    'partner_id': stock_pickingg.partner_id.name,
                    'curr_name' : curr_name, 
                    'move_lines': move_lines,
                    'total': total,
                    })
             
        if delivery_sortby_ids:
            for delivery_sortby in delivery_sortby_obj.browse(cr, uid, delivery_sortby_ids):
                results1 = results1 and sorted(results1, key=lambda val_res: val_res[delivery_sortby.sort_by]) or []
        else:
            results1 = results1 and sorted(results1, key=lambda val_res: val_res['curr_name']) or []
            results1 = results1 and sorted(results1, key=lambda val_res: val_res['partner_id']) or []
            results1 = results1 and sorted(results1, key=lambda val_res: val_res['name']) or []
            results1 = results1 and sorted(results1, key=lambda val_res: val_res['origin']) or []
            results1 = results1 and sorted(results1, key=lambda val_res: val_res['date_done']) or []
           
        for rs1 in results1:
          
            row += 1 
            sheet.write_merge(row, row, 0, 9, '', report_tstyle1)
             
            row+=1
    #             sheet.row(row).height = 400
            sheet.write(row, 0, 'Delivery Date', header_tstyle_c)
            sheet.write(row, 1, 'SO No.', header_tstyle_c)
            sheet.write(row, 2, 'DO No.', header_tstyle_c)
            sheet.write(row, 3, 'Invoice No.', header_tstyle_c)
            sheet.write(row, 4, 'Customer', header_tstyle_c)
            sheet.write(row, 5, 'Description', header_tstyle_c)
            sheet.write(row, 6, 'Quantity', header_tstyle_c)
            sheet.write(row, 7, 'Currency', header_tstyle_c)
            sheet.write(row, 8, 'Unit Price', header_tstyle_c)
            sheet.write(row, 9, 'Subtotal', header_tstyle_c)
         
            for rs3 in rs1['move_lines']:
                row += 1 
                sheet.write(row, 0, rs1['date_done'][:10], view_tstyle_l)
                sheet.write(row, 1, rs1['origin'], view_tstyle_l)
                sheet.write(row, 2, rs1['name'], view_tstyle_l)
                sheet.write(row, 3, '', view_tstyle_l)
                sheet.write(row, 4, rs1['partner_id'], view_tstyle_l)
                sheet.write(row, 5, rs3['description'], view_tstyle_l)
                sheet.write(row, 6, rs3['product_qty'],  unit_of_measure_ttl_format_tlrb_view)
                sheet.write(row, 7, rs3['currency_name'],view_tstyle_c)
                sheet.write(row, 8, rs3['price_unit'],unit_price_ttl_format_tlrb_view)
                sheet.write(row, 9, rs3['subtotal'], currency_ttl_format_tlrb_view)
                 
            row += 1 
            sheet.write(row, 8, 'TOTAL', view_tstyle_r_b_170)
            sheet.write(row, 9, rs1['total'], unit_price_r_b_view)
        
        row += 1 
        sheet.write_merge(row, row, 0, 9, '', report_tstyle1)
          
        row += 1
        sheet.write_merge(row, row, 0, 9, 'End of Report', text_tstyle_Top_l) 
         
        stream = cStringIO.StringIO()
        workbook.save(stream) 
         
        cr.execute(""" DELETE FROM output""")
         
        attach_id = self.pool.get('output').create(cr, uid, {'name': name_file, 'xls_output':base64.encodestring(stream.getvalue())})
        return {
                'name': _('Notification'),
                'context': context,
                'view_type': 'form',
                'view_mode': 'form',
                'res_model': 'output',
                'res_id':attach_id,
                'type': 'ir.actions.act_window',
                'target':'new'
                }
          
class delivery_sortby(osv.osv_memory):
    
    _name = "delivery.sortby"
    
    _columns = {
        'sortby_id': fields.many2one('delivery.report', 'Sort By', required=True, ondelete='cascade'),
        'seq': fields.integer('Sequence', required=True),
        'sort_by': fields.selection([('date_done','Delivery Date'),('origin', 'SO No.'), ('name', 'DO No.'), ('partner_id', 'Customer'), ('curr_name', 'Currency')],'Sort by', required=True),
    }     
    
delivery_report()
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
